package com.cassiopae.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.cassiopae.entity.Profile;
import com.cassiopae.entity.User;
import com.cassiopae.dao.ConnectionDAO;


public class UserDaoImpl extends IDumpDAO<User>{

	private Statement st;
	private ProfileDaoImpl profileDaoImpl = new ProfileDaoImpl();


	public Boolean create(User user) {
		Connection cn = ConnectionDAO.openConnection();
		String requete = "INSERT INTO user (`nom`,`prenom`,`login`,`password`,`email`) VALUES ('"+user.getNom()+"','"+user.getPrenom()+"','"+user.getLogin()+"','"+user.getPwd()+"','"+user.getEmail()+"')";
		Boolean rep = false;
		if (cn != null){

			try {
				st = cn.createStatement();
				st.executeUpdate(requete);
				cn.close();
				st = null;
				rep= true;
			} catch (SQLException e) {
				rep=false;
			}
		}
		return rep;
	}

	public User connectUser(String login, String password){
		Connection cn = ConnectionDAO.openConnection();
		ResultSet resultSet = null;
		User user = null;
		String requete = "SELECT nom, prenom, activateornot, type, email FROM USER WHERE login = '"+login+"' AND password='"+password+"'";
		if (cn != null){
			try {
				st = cn.createStatement();
				resultSet = st.executeQuery(requete);
				if(resultSet.next()){
					user = new User();
					user.setLogin(login);
					user.setPwd(password);
					user.setNom( resultSet.getString( "NOM" ));
					user.setPrenom( resultSet.getString( "PRENOM" ));
					user.setActivateOrNot( resultSet.getBoolean("ACTIVATEORNOT"));
					user.setType( resultSet.getString( "TYPE" ));
					user.setEmail( resultSet.getString( "EMAIL" ));
				}
				cn.close();
				st.close();
				retrieveAllProfiles( user );
			} catch (SQLException e) {
				user = null;
			}
		}
		return user;
	}

	public void retrieveAllProfiles(User user) {
		user.setProfiles(new ArrayList<Profile>());
		
		if( "U".equals( user.getType() ) ){
			List<String> schemas = getAllSchemaForUser( user.getEmail() );
			for(String schema : schemas){
				List<Profile> profiles = profileDaoImpl.getProfilesBySchema(schema);
				user.getProfiles().addAll(profiles);
			}
		}else{
			user.getProfiles().addAll( profileDaoImpl.getAll() );
		}
		
	}

	private List<String> getAllSchemaForUser(String _email) {
		
		List<String> schemas = new ArrayList<String>();
		Connection cn = ConnectionDAO.openConnection();
		ResultSet resultSet = null;
		String requete = "SELECT id_schema FROM USER_SCHEMA WHERE id_user = '"+_email+"'";
		if (cn != null){
			try {
				st = cn.createStatement();
				resultSet = st.executeQuery(requete);
				while(resultSet.next()){
					String schema = resultSet.getString( "ID_SCHEMA" );
					schemas.add(schema);
				}
				cn.close();
				st.close();
			} catch (SQLException e) {
				schemas = new ArrayList<String>();
			}
		}
		return schemas;
	}

	public List<User> getAllWithoutLogin(String _email) {

		List<User> users = new ArrayList<User>();
		Connection cn = ConnectionDAO.openConnection();
		ResultSet resultSet = null;
		String requete = "SELECT nom, prenom, password, activateornot, type, login, email FROM USER WHERE email <> '"+_email+"'";
		if (cn != null){
			try {
				st = cn.createStatement();
				resultSet = st.executeQuery(requete);
				while(resultSet.next()){
					String nom = resultSet.getString( "NOM" );
					String prenom = resultSet.getString( "PRENOM" );
					String password = resultSet.getString("PASSWORD");
					Boolean activeornot = resultSet.getBoolean("ACTIVATEORNOT");
					String type = resultSet.getString("TYPE" );
					String login = resultSet.getString("LOGIN" );
					String email = resultSet.getString("EMAIL");
					User user = new User(nom, prenom, login, email, password, activeornot, type);
					users.add(user);
				}
				cn.close();
				st.close();
			} catch (SQLException e) {
				users = new ArrayList<User>();
			}
		}
		return users;
	}

	public void activeUser(String email, Boolean active) {
		Connection cn = ConnectionDAO.openConnection();
		Character activeString = active ? '1' : '0';
		String requete = "UPDATE USER SET ACTIVATEORNOT='"+activeString+"' WHERE EMAIL='"+email+"' ";
		if (cn != null){
			try {
				st = cn.createStatement();
				st.executeUpdate(requete);
				cn.close();
				st = null;
			} catch (SQLException e) {
			}
		}

	}

	public void updatePassword(String pwd, String login){
		Connection cn = ConnectionDAO.openConnection();
		String requete = "UPDATE USER SET PASSWORD='"+pwd+"' WHERE LOGIN='"+login+"'";
		if (cn != null){
			try {
				st = cn.createStatement();
				st.executeUpdate(requete);
				cn.close();
				st = null;
			} catch (SQLException e) {
			}
		}
	}


	public Boolean delete(String email) {
		Connection cn = ConnectionDAO.openConnection();
		String requete = "DELETE FROM USER WHERE EMAIL='"+email+"'";
		Boolean rep = false;
		if ( cn != null && profileDaoImpl.deleteProfilesForUser(email) ){

			try {
				st = cn.createStatement();
				st.executeUpdate(requete);
				cn.close();
				st = null;
				rep= true;
			} catch (SQLException e) {
				rep=false;
			}
		}
		return rep;
	}

	public void updateUser(String nom, String prenom, String login){
		Connection cn = ConnectionDAO.openConnection();
		PreparedStatement ps = null;
		String updateTable = "UPDATE USER SET NOM = ?, PRENOM = ?, LOGIN = ? WHERE LOGIN = ?";
		if (cn != null){
			try {
				ps = cn.prepareStatement(updateTable);
				ps.setString(1, nom);
				ps.setString(2, prenom);
				ps.setString(3, login);
				ps.executeUpdate();
				cn.close();
				ps = null;
			} catch (SQLException e) {
			}
		}
	}

	public boolean addSchemasToUser(User user, List<String> schemaUser) {
		
		Connection cn = ConnectionDAO.openConnection();
		st = null;
		Boolean rep = false;
		for(String schema : schemaUser){
			String requete = "INSERT INTO user_schema (`id_schema`,`id_user`) VALUES ('"+schema+"','"+user.getEmail()+"')";
			
			if (cn != null){

				try {
					st = cn.createStatement();
					st.executeUpdate(requete);
					rep= true;
				} catch (SQLException e) {
					rep=false;
				}
			}
		}
		try {
			cn.close();
		} catch (SQLException e) {}
		return rep;

	}


	@Override
	public void update(User user) {
		// TODO Auto-generated method stub

	}


	@Override
	public List<User> getAll() {
		// TODO Auto-generated method stub
		return null;
	}


	@Override
	public User getById(int id) {
		return null;
	}


	@Override
	public Boolean delete(User t) {
		// TODO Auto-generated method stub
		return null;
	}



}
